import express from 'express'
import { query } from '../config/database.js'

const router = express.Router()

// 获取所有比赛
router.get('/', async (req, res) => {
  try {
    const rows = await query(`
      SELECT 
        m.id,
        DATE_FORMAT(m.match_date, '%Y-%m-%d') as match_date,
        TIME_FORMAT(m.match_time, '%H:%i') as match_time,
        m.week_number,
        m.venue_id,
        m.match_schedule_id,
        m.home_school_id,
        m.away_school_id,
        m.home_group_id,
        m.away_group_id,
        m.status,
        m.created_at,
        m.updated_at,
        v.name as venue_name,
        v.location as venue_location,
        hs.name as home_school_name,
        hs.short_name as home_school_short_name,
        aws.name as away_school_name,
        aws.short_name as away_school_short_name,
        hg.group_code as home_group_code,
        ag.group_code as away_group_code,
        ms.schedule_name,
        ms.week_number as schedule_week_number,
        hgl.name as home_grade_level_name,
        agl.name as away_grade_level_name
      FROM matches m
      LEFT JOIN venues v ON m.venue_id = v.id
      LEFT JOIN schools hs ON m.home_school_id = hs.id
      LEFT JOIN schools aws ON m.away_school_id = aws.id
      LEFT JOIN schedule_groups hg ON m.home_group_id = hg.id
      LEFT JOIN schedule_groups ag ON m.away_group_id = ag.id
      LEFT JOIN match_schedules ms ON m.match_schedule_id = ms.id
      LEFT JOIN grade_levels hgl ON hg.grade_level_id = hgl.id
      LEFT JOIN grade_levels agl ON ag.grade_level_id = agl.id
      ORDER BY m.match_date ASC, m.match_time ASC
    `)
    res.json(rows)
  } catch (error) {
    console.error('Error:', error)
    res.status(500).json({ error: error.message })
  }
})

// 创建新比赛
router.post('/', async (req, res) => {
  try {
    const { 
      match_date, 
      match_time, 
      venue_id, 
      match_schedule_id,
      home_school_id,
      away_school_id,
      home_group_id,
      away_group_id,
      status 
    } = req.body

    // 验证主客场学校不能相同
    if (home_school_id === away_school_id) {
      return res.status(400).json({ error: '主客场学校不能相同' })
    }

    const result = await query(
      'INSERT INTO matches (match_date, match_time, venue_id, match_schedule_id, home_school_id, away_school_id, home_group_id, away_group_id, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [match_date, match_time, venue_id, match_schedule_id, home_school_id, away_school_id, home_group_id, away_group_id, status]
    )
    res.json({ id: result.insertId })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

export default router 